﻿using System.Collections.Generic;
using System.Linq;
using System.Text;
using eAtlantida.Tools.Generator.Sql;

namespace eAtlantida.Tools.Generator
{
    public class SqlGenerator
    {
        private readonly string name;

        private readonly IEnumerable<ColumnInformation> _columns;

        public SqlGenerator(IEnumerable<ColumnInformation> columns, string name)
        {
            _columns = columns;
            this.name = name;
        }

        /// <summary>
        /// Generates the insert procedure.
        /// </summary>
        public string GenerateInsertProcedure()
        {
            var result = new StringBuilder();
            result.AppendLine("CREATE PROCEDURE [dbo].[", name.ToLowerFirstChar(), "_insert]");

            result.Append(string.Join(
                         ",\n",
                _columns.Where(x => !x.IsIdentity).Select(
                    x => StringBuilderExtension.Tab + "@" + x.Name.ToLowerFirstChar() + " " + x.DataTypeName)));

            result.Append("\nAS\nINSERT INTO [dbo].[" + name.ToLowerFirstChar() + "]\n(");

            result.Append(string.Join(",\n", _columns.Where(x => !x.IsIdentity).Select(x => StringBuilderExtension.Tab + x.Name)));

            result.Append(")\nVALUES(\n");

            result.Append(
                string.Join(
                    ",\n",
                    _columns.Where(x => !x.IsIdentity).Select(
                        x => StringBuilderExtension.Tab + "@" + x.Name.ToLowerFirstChar())));

            result.Append(")\n");

            var indentiry = _columns.FirstOrDefault(x => x.IsIdentity);

            if (indentiry != null)
            {
                result.Append("declare @" + indentiry.Name.ToLowerFirstChar() + " " + indentiry.DataTypeName
                         + " = " + "@@IDENTITY\n");
            }

            result.Append(GenerateSelectByPrimaryKey());

            return result.Append("\nRETURN 0\n").ToString();
        }

        public string GenerateListingProcedure()
        {
            var result = new StringBuilder();

            result.AppendLine("CREATE PROCEDURE [dbo].[" + name.ToLowerFirstChar() + "_get_listing]");
            result.AppendLine(1, "@page int = 0,");
            result.AppendLine(1, "@size int = 0,");
            result.AppendLine(1, "@column nvarchar(256),");
            result.AppendLine(1, "@direction int = 0,");

            var isAdded = false;
            foreach (var column in _columns)
            {
                if (!column.IsIdentity)
                {
                    if (!isAdded)
                    {
                        isAdded = true;
                    }
                    else
                    {
                        result.AppendLine(",");
                    }
                    result.Append(1, "@", column.Name.ToLowerFirstChar(), " ", column.DataTypeName, " = ", GetDefaultValue(column.DataTypeName));
                }
            }
            result.AppendLine();
            result.AppendLine("AS");
            result.AppendLine("SELECT");
            isAdded = false;
            foreach (var column in _columns)
            {
                if (!isAdded)
                {
                    isAdded = true;
                }
                else
                {
                    result.AppendLine(",");
                }
                result.Append(1, column.Name);
            }
            result.AppendLine();
            result.AppendLine("FROM ", name);
            result.AppendLine("WHERE");
            result.Append(GenerateWhereForListing());
            result.AppendLine("ORDER BY");
            isAdded = false;
            foreach (var column in _columns)
            {
                if (column.IsIdentity)
                    continue;

                if (!isAdded)
                {
                    isAdded = true;
                }
                else
                {
                    result.AppendLine(",");
                }
                result.AppendLine(1, "CASE WHEN @column = '", column.Name, "' AND @direction = 1 THEN ", column.Name, " END DESC,");
                result.Append(1, "CASE WHEN @column = '", column.Name, "' AND @direction = 0 THEN ", column.Name, " END ASC");
            }
            result.AppendLine();
            result.AppendLine("OFFSET ((@page-1)*@size) ROWS");
            result.AppendLine("FETCH NEXT @size ROWS ONLY;");

            var identity = _columns.FirstOrDefault(x => x.IsIdentity);
            if (identity != null)
            {
                result.AppendLine();
                result.AppendLine("DECLARE");
                result.AppendLine(1, "@TotalCount int");
                result.AppendLine();
                result.AppendLine("SELECT @TotalCount = Count(DISTINCT ", identity.Name, ")");
                result.AppendLine("FROM ", name);
                result.AppendLine("WHERE");
                result.Append(GenerateWhereForListing());
                result.AppendLine("RETURN @TotalCount;");
            }

            return result.ToString();
        }

        /// <summary>
        /// Generates the delete procedure.
        /// </summary>
        public string GenerateDeleteProcedure()
        {
            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + name.ToLowerFirstChar() + "_delete]\n");
            var indentity = _columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("@" + indentity.Name.ToLowerFirstChar() + " " + indentity.DataTypeName + "\n");
            }
            else
            {
                var primaries = _columns.Where(x => x.IsPrimaryKey).ToList();
                int i = 0;
                foreach (var primary in primaries)
                {
                    result.Append(1, "@" + primary.Name.ToLowerFirstChar() + " " + primary.DataTypeName);
                    if (i < primaries.Count - 1)
                    {
                        result.Append(",");
                    }
                    result.Append("\n");
                    i++;
                }
            }

            result.Append("\nAS\nDELETE FROM [dbo].[" + name.ToLowerFirstChar() + "]\n");
            indentity = _columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("\nWHERE\n");

                result.Append(1, name + ".[" + indentity.Name + "]=@" + indentity.Name.ToLowerFirstChar());
            }
            else
            {
                var primaries = _columns.Where(x => x.IsPrimaryKey).ToList();
                result.Append("\nWHERE\n");
                int i = 0;
                foreach (var primary in primaries)
                {
                    result.Append(StringBuilderExtension.Tab + "(" + name + ".[" + primary.Name + "]=@" + primary.Name.ToLowerFirstChar()
                             + " )");
                    if (i < primaries.Count - 1)
                    {
                        result.Append(" AND\n");
                    }
                    i++;
                }
            }

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the update procedure.
        /// </summary>
        public string GenerateUpdateProcedure()
        {
            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + name.ToLowerFirstChar() + "_update" + "]\n");

            result.Append(string.Join(
                         ",\n", _columns.Select(x => StringBuilderExtension.Tab + "@" + x.Name.ToLowerFirstChar() + " " + x.DataTypeName)));

            result.Append("\nAS\nUPDATE [dbo].[" + name + "] SET ");

            result.Append(
                     string.Join(
                         ",\n",
                         _columns.Where(x => !x.IsIdentity).Select(
                             x => "\t[dbo].[" + name + "].[" + x.Name + "] = @" + x.Name.ToLowerFirstChar())));

            result.Append("\n");

            ColumnInformation indentity = _columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("WHERE\n");

                result.Append(1, "[dbo].[" + name + "].[" + indentity.Name + "]=@" + indentity.Name.ToLowerFirstChar());
            }
            else
            {
                var primaries = _columns.Where(x => x.IsPrimaryKey).ToList();
                result.Append("WHERE\n");
                int i = 0;
                foreach (var primary in primaries)
                {
                    result.Append(1, "(" + name + ".[" + primary.Name + "]=@" + primary.Name.ToLowerFirstChar() + " )");
                    if (i < primaries.Count - 1)
                    {
                        result.Append(" AND\n");
                    }
                    i++;
                }
            }

            result.Append(GenerateSelectByPrimaryKey());

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the select all procedure.
        /// </summary>
        public string GenerateSelectAllProcedure()
        {

            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + name.ToLowerFirstChar() + "_select_all" + "]\n");
            result.Append("\nAS\nSELECT ");

            result.Append(string.Join(",\n", _columns.Select(x => StringBuilderExtension.Tab + "[dbo].[" + name + "].[" + x.Name + "]")));

            result.Append("\nFROM " + name + " \n");

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the select by id procedure.
        /// </summary>
        public string GenerateSelectByIdProcedure()
        {
            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + name.ToLowerFirstChar() + "_select_by_id" + "]\n");

            result.Append(string.Join(
                         ",\n",
                         _columns.Where(x => x.IsIdentity || x.IsPrimaryKey).Select(
                             x => StringBuilderExtension.Tab + "@" + x.Name.ToLowerFirstChar() + " " + x.DataTypeName)));

            result.Append("\nAS ");

            result.Append(GenerateSelectByPrimaryKey());

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the select by primary key.
        /// </summary>
        private string GenerateSelectByPrimaryKey()
        {
            var result = new StringBuilder("\nSELECT\n");

            result.Append(string.Join(",\n", _columns.Select(x => StringBuilderExtension.Tab + "[" + x.Name + "]")));

            result.Append("\nFROM [dbo].[" + name + "] \n");

            var indentity = _columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("WHERE\n");

                result.Append(1, name + ".[" + indentity.Name + "]=@" + indentity.Name.ToLowerFirstChar());
            }
            else
            {
                var primaries = _columns.Where(x => x.IsPrimaryKey).ToList();
                result.Append("WHERE\n");
                int i = 0;
                foreach (var primary in primaries)
                {
                    result.Append(1, "(" + name + ".[" + primary.Name + "]=@" + primary.Name.ToLowerFirstChar() + " )");
                    if (i < primaries.Count - 1)
                    {
                        result.AppendLine(" AND");
                    }
                    i++;
                }
            }
            return result.ToString();
        }

        private StringBuilder GenerateWhereForListing()
        {
            var result = new StringBuilder();

            var isAdded = false;
            foreach (var column in _columns)
            {
                if (column.IsIdentity)
                    continue;

                if (!isAdded)
                {
                    isAdded = true;
                }
                else
                {
                    result.AppendLine(" AND");
                }
                if (column.DataTypeName.StartsWith("varchar") ||
                    column.DataTypeName.StartsWith("nvarchar"))
                {
                    result.Append(1, "(@", column.Name.ToLowerFirstChar(), " IS NULL OR ", column.Name, " LIKE '%' + @", column.Name.ToLowerFirstChar(), "+ '%')");
                }
                else
                {
                    switch (column.DataTypeName)
                    {
                        case "int":
                        case "float":
                        case "tinyint":
                            result.Append(1, "(@", column.Name.ToLowerFirstChar(), " = 0 OR ", column.Name, " = @", column.Name.ToLowerFirstChar(), ")");
                            break;
                        case "date":
                        case "datetime":
                        case "datetime2":
                            result.Append(1, "(@", column.Name.ToLowerFirstChar(), " IS NULL OR DATEPART(YEAR, @", column.Name.ToLowerFirstChar(), ") < 1753 OR ", column.Name, " = @", column.Name.ToLowerFirstChar(), ")");
                            break;
                    }
                }
            }
            result.AppendLine();

            return result;
        }

        private static string GetDefaultValue(string typeName)
        {
            switch (typeName)
            {
                case "int":
                case "float":
                case "tinyint":
                    return "0";
                default:
                    return "null";
            }
        }
    }
}